using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

namespace MsSqlSpatial.Utility
{
    /// <summary>
    /// Customized SQL Assembly Deployment Script Generation.
    /// </summary>
    internal class Deployment
    {
        #region Delegates

        public delegate void ProgressMessageEventHandler(string message);

        #endregion

        private const string authorizationRole = "public";
        private const string schemaName = "ST";

        private const string scriptGo = @"
GO

";

        private const string scriptIfErrorRollback = @"IF (@@ERROR <> 0)
		ROLLBACK TRANSACTION
	";

        private readonly List<string> publicTokens = new List<string>();

        public event ProgressMessageEventHandler ProgressMessage;

        public void DeployAssembly(string assemblyFilename, string connectionString, string database,
                                   DeployPermission deployPermission)
        {
            DeployAssemblies(new[] { assemblyFilename }, connectionString, database, deployPermission);
        }

        public void DeployAssemblies(string[] assemblyFilename, string connectionString, string database,
                                     DeployPermission deployPermission)
        {
            var builder = new StringBuilder();
            ProgressMessage("Creating scripts");
            Build(assemblyFilename, builder, database, deployPermission);
            ProgressMessage("Establishing connection");
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        command.Transaction = transaction;
                        command.CommandTimeout = 120;
                        DropAssemblyDependiencies(command);
                        RunScript(builder.ToString(), command);
                        RecreateAssemblyDependiencies(command);
                        ProgressMessage("Creating metadata tables");
                        StoredProcedures.ExecuteCreateMetaTables(command);
                    }
                }
                catch
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch
                    {
                    }
                    throw;
                }
                try
                {
                    transaction.Commit();
                }
                catch (InvalidOperationException)
                {
                    // Expected exception, do nothing.
                }
            }
        }

        private void DropAssemblyDependiencies(SqlCommand command)
        {
            ProgressMessage("Dropping assembly dependencies");

            SpatialRefSysTable.ExecuteDropRefreshTransfromCacheTrigger(command);

            command.CommandText = @"SELECT * FROM ST.GEOMETRY_COLUMNS";
            command.CommandType = CommandType.Text;
            command.Parameters.Clear();
            var ds = new DataSet();
            var adapter = new SqlDataAdapter(command);
            try
            {
                adapter.Fill(ds);
            }
            catch
            {
            }
            if (ds.Tables.Count != 0 && ds.Tables[0].Rows.Count != 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    var schemaName = (string)row["F_TABLE_SCHEMA"];
                    var tableName = (string)row["F_TABLE_NAME"];
                    var columnName = (string)row["F_GEOMETRY_COLUMN"];
                    GeometryTable.ExecuteDropIndexes(command, schemaName, tableName, columnName);
                    GeometryTable.ExecuteDropConstraintGeometryType(command, schemaName, tableName, columnName);
                    GeometryTable.ExecuteDropConstraintSRID(command, schemaName, tableName, columnName);
                    GeometryTable.ExecuteDropTrigger(command, schemaName, tableName, columnName);
                    StoredProcedures.ExecuteDropGeometryQueryFunctions(command, schemaName, tableName, columnName);
                }
            }
        }

        private void RecreateAssemblyDependiencies(SqlCommand command)
        {
            SpatialRefSysTable.ExecuteCreateRefreshTransfromCacheTrigger(command);

            command.CommandText = @"SELECT * FROM ST.GEOMETRY_COLUMNS";
            command.CommandType = CommandType.Text;
            command.Parameters.Clear();
            var ds = new DataSet();
            var adapter = new SqlDataAdapter(command);
            try
            {
                adapter.Fill(ds);
            }
            catch
            {
            }
            if (ds.Tables.Count != 0 && ds.Tables[0].Rows.Count != 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    var schemaName = (string)row["F_TABLE_SCHEMA"];
                    var tableName = (string)row["F_TABLE_NAME"];
                    var columnName = (string)row["F_GEOMETRY_COLUMN"];
                    var type = (string)row["GEOMETRY_TYPE"];
                    var srid = (int)row["SRID"];
                    string objectName = schemaName + "." + tableName + "." + columnName;
                    ProgressMessage("Creating indexes on " + objectName);
                    GeometryTable.ExecuteAddIndexes(command, schemaName, tableName, columnName);
                    ProgressMessage("Creating constraints on " + objectName);
                    GeometryTable.ExecuteAddConstraintGeometryType(command, schemaName, tableName, columnName, type);
                    GeometryTable.ExecuteAddConstraintSRID(command, schemaName, tableName, columnName, srid);
                    ProgressMessage("Creating triggers on " + objectName);
                    GeometryTable.ExecuteCreateTrigger(command, schemaName, tableName, columnName);
                    ProgressMessage("Creating functions on " + objectName);
                    StoredProcedures.ExecuteAddGeometryQueryFunctions(command, schemaName, tableName, columnName);
                }
            }
        }

        public void UndeployAssembly(string assemblyFilename, string connectionString, string database)
        {
            UndeployAssemblies(new[] { assemblyFilename }, connectionString, database);
        }

        public void UndeployAssemblies(string[] assemblyFilenames, string connectionString, string database)
        {
            ProgressMessage("Creating scripts");
            var builder = new StringBuilder();
            Array.ForEach(assemblyFilenames,
                          (assemblyFilename) => { BuildDropAssembly(builder, assemblyFilename, database, true); });
            ProgressMessage("Establishing connection");
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        command.Transaction = transaction;
                        command.CommandTimeout = 120;
                        ProgressMessage("Backing up metadata tables");
                        StoredProcedures.ExecuteBackupMetaTables(command);
                        DropAssemblyDependiencies(command);
                        RunScript(builder.ToString(), command);
                    }
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
                try
                {
                    transaction.Commit();
                }
                catch (InvalidOperationException)
                {
                    // Expected exception, do nothing.
                }
            }
        }

        private void RunScript(string script, SqlCommand command)
        {
            var regex = new Regex("\r\nGO\r\n");
            string[] subscripts = regex.Split(script);
            int i = 0;
            foreach (string subscript in subscripts)
            {
                ProgressMessage(string.Format("Running script {0} of {1}", ++i, subscripts.Length));
                try
                {
                    command.CommandText = subscript;
                    command.CommandType = CommandType.Text;
                    command.Parameters.Clear();
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new ApplicationException("Exception occured executing script:\n" + subscript, ex);
                }
            }
        }

        public void Build(string[] assemblyFilename, string scriptFilename, string database,
                          DeployPermission deployPermission)
        {
            using (var scriptWriter = new StreamWriter(scriptFilename, false, Encoding.UTF8))
            {
                var builder = new StringBuilder();
                Build(assemblyFilename, builder, database, deployPermission);
                scriptWriter.Write(builder.ToString());
            }
        }


        /// <summary>
        /// Notes: edited by John Diss (18 jan 2008) to send the assembly bytes instead of the filename. 
        /// This should allow for the assembly to be deployed from a client machine.
        /// Existing limitation was that the app had to be run from the server.
        /// </summary>
        /// <param name="assemblyFilename"></param>
        /// <param name="builder"></param>
        /// <param name="database"></param>
        /// <param name="deployPermission"></param>
        public void Build(string[] assemblyFilenames, StringBuilder builder, string database,
                          DeployPermission deployPermission)
        {
            publicTokens.Clear();

            var reversed = new List<string>(assemblyFilenames);
            reversed.Reverse();
            reversed.ForEach((assemblyFilename) => BuildDropAssembly(builder, assemblyFilename, database, false));

            Array.ForEach(assemblyFilenames,
                          (assemblyFilename) => BuildPrivate(assemblyFilename, builder, database, deployPermission));
        }


        public void Build(string assemblyFilename, StringBuilder builder, string database,
                          DeployPermission deployPermission)
        {
            publicTokens.Clear();
            BuildDropAssembly(builder, assemblyFilename, database, false);
            BuildPrivate(assemblyFilename, builder, database, deployPermission);
        }

        private string GetByteString(byte[] bytes)
        {
            string s = "0x" + BitConverter.ToString(bytes).Replace("-", "");
            return s;
        }

        private void BuildPrivate(string assemblyFilename, StringBuilder builder, string database,
                                  DeployPermission deployPermission)
        {
            Assembly assembly = Assembly.LoadFile(assemblyFilename);
            string assemblyName = (assembly.GetName()).Name;
            string authorizationSql = " AUTHORIZATION [" + SafeSqlIdentifier(authorizationRole) + "]";
            string deployPermissionSql = deployPermission == DeployPermission.ExternalAccess
                                             ? "EXTERNAL_ACCESS"
                                             : deployPermission.ToString().ToUpper();


            // Some links about assembly permissions:
            // http://msdn2.microsoft.com/en-us/library/ms345106.aspx
            // http://msdn2.microsoft.com/en-us/library/ms403273.aspx
            // http://msdn2.microsoft.com/en-us/library/ms345101.aspx
            // http://msdn2.microsoft.com/en-us/library/ms187861.aspx
            // http://msdn2.microsoft.com/en-us/library/ms345105.aspx
            // http://msdn2.microsoft.com/en-us/library/ms345097.aspx

            if (deployPermission != DeployPermission.Safe)
            {
                string publicKeyToken = BitConverter.ToString(assembly.GetName().GetPublicKeyToken());
                if (!publicTokens.Contains(publicKeyToken) && assemblyName.Contains("MsSqlSpatial"))
                {
                    publicTokens.Add(publicKeyToken);
                    builder.AppendFormat(
                        @"
USE master

IF NOT EXISTS (SELECT name FROM sys.asymmetric_keys WHERE name=N'{1}Key')
CREATE ASYMMETRIC KEY [{1}Key]
FROM EXECUTABLE FILE = N'{2}'

IF NOT EXISTS (SELECT name FROM sys.syslogins WHERE name=N'{1}Login')
BEGIN
	CREATE LOGIN [{1}Login] FROM ASYMMETRIC KEY [{1}Key]
	REVOKE CONNECT SQL FROM [{1}Login]
END
	GRANT {3} ASSEMBLY TO [{1}Login]

{0}"
                        , scriptGo
                        , SafeSqlIdentifierAndQuotes(assemblyName)
                        , assemblyFilename
                        , deployPermissionSql.Replace("_", " ")
                        );
                }
            }

            builder.AppendFormat(
                @"USE {1}
{0}"
                , scriptGo
                , database
                );


            builder.AppendFormat(
                @"IF (@@TRANCOUNT > 0)
		AND NOT EXISTS (SELECT name FROM sys.assemblies WHERE name=N'{1}')
BEGIN
EXEC sp_executesql N'
	CREATE ASSEMBLY [{1}]{2}
		FROM {3}
		WITH PERMISSION_SET = {5}'
{4}
END
{0}
"
                , scriptGo
                , SafeSqlIdentifierAndQuotes(assemblyName)
                , SafeSqlQuotes(authorizationSql)
                , GetByteString(File.ReadAllBytes(assemblyFilename)) //, assemblyFilename
                , scriptIfErrorRollback
                , deployPermissionSql
                );

            string dbgPath = assemblyFilename.Replace(".dll", ".pdb");
            if (File.Exists(dbgPath))
            {
                string byteString = GetByteString(File.ReadAllBytes(dbgPath));

                builder.AppendFormat(
                    @"
            ALTER ASSEMBLY [{0}]
            ADD FILE FROM {1} AS '{2}'
            {3}
    
        ",
                    SafeSqlIdentifierAndQuotes(assemblyName),
                    byteString,
                    Path.GetFileName(dbgPath),
                    scriptGo);
            }

            builder.AppendFormat(
                @"IF (@@TRANCOUNT > 0)
BEGIN
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'{0}')
	EXEC sys.sp_executesql N'CREATE SCHEMA [{0}] AUTHORIZATION [{1}]'
END
{2}"
                , schemaName
                , authorizationRole
                , scriptGo
                );

            if (assemblyFilename.EndsWith("MsSqlSpatialLibrary.dll"))
            {
                BuildSqlTypes(assemblyName, GetSqlTypes(assembly, typeof(SqlUserDefinedTypeAttribute)), builder);
                BuildSqlProcedures(assemblyName, GetSqlMethods(assembly, typeof(SqlProcedureAttribute)), builder);
                BuildSqlFunctions(assemblyName,
                                  GetSqlMethods(assembly, typeof(SqlFunctionAttribute), typeof(SqlMethodAttribute)),
                                  builder);
                BuildSqlTriggers(assemblyName, GetSqlMethods(assembly, typeof(SqlTriggerAttribute)), builder);
                BuildSqlAggregates(assemblyName, GetSqlTypes(assembly, typeof(SqlUserDefinedAggregateAttribute)),
                                   builder);
                BuildAddGeometryQueryFunctions(GetSqlMethods(assembly, typeof(SpatialQueryAttribute)), builder);
                BuildDropGeometryQueryFunctions(GetSqlMethods(assembly, typeof(SpatialQueryAttribute)), builder);
            }
        }

        public void BuildDropAssembly(StringBuilder builder, String assemblyFilename, string database, bool full)
        {
            Assembly assembly = Assembly.LoadFile(assemblyFilename);
            string assemblyName = assembly.GetName().Name;
            Stream resource =
                Assembly.GetExecutingAssembly().GetManifestResourceStream("MsSqlSpatial.Utility.DropAssembly.sql");
            string script = new StreamReader(resource).ReadToEnd();
            var template = new StringBuilder();
            template.AppendFormat(script
                                  , SafeSqlQuotes(assemblyName)
                                  , SafeSqlIdentifier(assemblyName)
                                  , schemaName
                                  , full ? "0=0" : "0=1"
                                  , database
                );
            builder.Append(template.ToString());
            builder.Append(scriptGo);
        }

        public void BuildAddGeometryQueryFunctions(MethodInfo[] methods, StringBuilder builder)
        {
            var queryFunctionsDdl = new StringBuilder();

            foreach (MethodInfo method in methods)
            {
                string parametersDdl = string.Empty;
                string parametersDml = string.Empty;
                int count = 0;
                foreach (ParameterInfo parameter in method.GetParameters())
                {
                    if (++count > 2)
                    {
                        string defaultValue = GetDefaultValue(parameter.GetCustomAttributes(true));
                        parametersDdl += "\t\t\t@" + parameter.Name + " " +
                                         GetNativeSqlType(parameter.ParameterType, parameter.GetCustomAttributes(true)) +
                                         defaultValue + ",\n";
                        parametersDml += ", @" + parameter.Name;
                    }
                }
                parametersDdl = parametersDdl.TrimEnd(',', '\n');
                parametersDml = parametersDml.TrimEnd(',');
                if (parametersDdl.Length > 0)
                    parametersDdl += '\n';

                var functionAttribute =
                    method.GetCustomAttributes(typeof(SqlFunctionAttribute), false)[0] as SqlFunctionAttribute;
                string functionName = (functionAttribute.Name != null) ? functionAttribute.Name : method.Name;

                var spatialQueryAttribute =
                    method.GetCustomAttributes(typeof(SpatialQueryAttribute), false)[0] as SpatialQueryAttribute;
                string spatialQueryColumns = (spatialQueryAttribute.Columns != null)
                                                 ? spatialQueryAttribute.Columns
                                                 : string.Empty;

                if (spatialQueryColumns.Length != 0)
                {
                    string[] columns = spatialQueryColumns.Split(',');
                    string alias = "," + schemaName + "_q.";
                    spatialQueryColumns = alias + string.Join(alias, columns);
                }

                queryFunctionsDdl.AppendFormat(
                    @"

SET @script = N'
	IF EXISTS (SELECT * FROM sys.objects WHERE name = N''{2}'' AND type = ''IF'')
		DROP FUNCTION [{1}].[{2}]
'
EXEC sp_executesql @script

SET @script = N'
	CREATE FUNCTION [{1}].[{2}]
	(
{3}	)
	RETURNS TABLE
	AS
	RETURN
		SELECT {1}_t.*{6}
		FROM [{1}].[{5}](''' + @schema_name + '.' + @table_name + ''', ''' + @column_name + '''{4}) AS {1}_q
		INNER JOIN [' + @schema_name + '].[' + @table_name + '] AS {1}_t
		ON {1}_q.[oid] = {1}_t.[' + @table_key + ']
'
EXEC sp_executesql @script

IF (@@ERROR <> 0)
	BEGIN
		ROLLBACK TRANSACTION
		RETURN -1
	END

"
                    , string.Empty
                    , schemaName
                    ,
                    SafeSqlIdentifierAndQuotes(functionName) +
                    "#' + @schema_name + '#' + @table_name + '#' + @column_name + '"
                    , SafeSqlQuotes(parametersDdl)
                    , SafeSqlQuotes(parametersDml)
                    , SafeSqlIdentifierAndQuotes(functionName)
                    , spatialQueryColumns
                    );
            }


            builder.AppendFormat(
                @"
		IF EXISTS (SELECT * FROM sys.objects WHERE name = N'{2}' AND type = 'S')
			DROP PROCEDURE [{1}].[{2}]
{0}
		CREATE PROCEDURE [{1}].[{2}]
			@schema_name nvarchar(4000) = 'dbo',
			@table_name nvarchar(4000),
			@column_name nvarchar(4000)
		AS
		BEGIN
			BEGIN TRANSACTION
			DECLARE @script nvarchar(max)
			DECLARE @functionName varchar(4000)
			DECLARE @table_key varchar(4000)

			SELECT @table_key = [COLUMN_NAME]
			FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]
			WHERE [TABLE_SCHEMA]=@schema_name
			AND [TABLE_NAME]=@table_name

{3}
			COMMIT TRANSACTION
		END
{0}"
                , scriptGo
                , schemaName
                , "AddGeometryQueryFunctions"
                , queryFunctionsDdl
                );
        }

        public void BuildDropGeometryQueryFunctions(MethodInfo[] methods, StringBuilder builder)
        {
            var queryFunctionsDdl = new StringBuilder();

            foreach (MethodInfo method in methods)
            {
                var functionAttribute =
                    method.GetCustomAttributes(typeof(SqlFunctionAttribute), false)[0] as SqlFunctionAttribute;
                string functionName = (functionAttribute.Name != null) ? functionAttribute.Name : method.Name;

                queryFunctionsDdl.AppendFormat(
                    @"
SET @script = N'
IF EXISTS (SELECT * FROM sys.objects WHERE name = N''{2}'' AND type = ''IF'')
	DROP FUNCTION [{1}].[{2}]
'
EXEC sp_executesql @script
IF (@@ERROR <> 0)
	BEGIN
		ROLLBACK TRANSACTION
		RETURN -1
	END

"
                    , string.Empty
                    , schemaName
                    ,
                    SafeSqlIdentifierAndQuotes(functionName) +
                    "#' + @schema_name + '#' + @table_name + '#' + @column_name + '"
                    );
            }


            builder.AppendFormat(
                @"
		CREATE PROCEDURE [{1}].[{2}]
			@schema_name nvarchar(4000) = 'dbo',
			@table_name nvarchar(4000),
			@column_name nvarchar(4000)
		AS
		BEGIN
			BEGIN TRANSACTION
			DECLARE @script nvarchar(max)
{3}
			COMMIT TRANSACTION
		END
{0}"
                , scriptGo
                , schemaName
                , "DropGeometryQueryFunctions"
                , queryFunctionsDdl
                );
        }

        private void BuildSqlAggregates(string assemblyName, Type[] types, StringBuilder builder)
        {
            foreach (Type type in types)
            {
                var attribute =
                    type.GetCustomAttributes(typeof(SqlUserDefinedAggregateAttribute), false)[0] as
                    SqlUserDefinedAggregateAttribute;
                string aggregateName = (attribute.Name != null) ? attribute.Name : type.Name;
                string parameterName = null;
                string parameterType = null;
                MethodInfo methodAccumulate = type.GetMethod("Accumulate",
                                                             BindingFlags.NonPublic | BindingFlags.Public |
                                                             BindingFlags.Static | BindingFlags.Instance);
                ParameterInfo[] parameters = methodAccumulate.GetParameters();
                parameterName = parameters[0].Name;
                parameterType = GetNativeSqlType(parameters[0].ParameterType, parameters[0].GetCustomAttributes(true));
                string returnType = null;
                MethodInfo methodTerminate = type.GetMethod("Terminate",
                                                            BindingFlags.NonPublic | BindingFlags.Public |
                                                            BindingFlags.Static | BindingFlags.Instance);
                returnType = GetNativeSqlType(methodTerminate.ReturnType,
                                              methodTerminate.ReturnTypeCustomAttributes.GetCustomAttributes(true));
                if (!returnType.Contains("(") && !returnType.Contains(")"))
                    returnType = "[" + SafeSqlIdentifier(returnType) + "]";
                builder.AppendFormat(
                    @"IF (@@TRANCOUNT > 0)
BEGIN
	EXEC sp_executesql N'
		CREATE AGGREGATE [{1}].[{2}]
		(
			@{3} {4}
		)
		RETURNS {5}
			EXTERNAL NAME [{6}].[{7}]'
	{8}
END
{0}"
                    , scriptGo
                    , schemaName
                    , SafeSqlIdentifierAndQuotes(aggregateName)
                    , SafeSqlQuotes(parameterName)
                    , SafeSqlQuotes(parameterType)
                    , SafeSqlQuotes(returnType)
                    , SafeSqlIdentifierAndQuotes(assemblyName)
                    , SafeSqlIdentifierAndQuotes(type.FullName)
                    , scriptIfErrorRollback
                    );
            }
        }

        private void BuildSqlFunctions(string assemblyName, MethodInfo[] methods, StringBuilder builder)
        {
            foreach (MethodInfo method in methods)
            {
                var functionAttribute =
                    method.GetCustomAttributes(typeof(SqlFunctionAttribute), false)[0] as SqlFunctionAttribute;
                string functionName = (functionAttribute.Name != null) ? functionAttribute.Name : method.Name;
                string parameters = string.Empty;
                foreach (ParameterInfo parameter in method.GetParameters())
                {
                    string defaultValue = GetDefaultValue(parameter.GetCustomAttributes(true));
                    parameters += "\t\t\t@" + parameter.Name + " " +
                                  GetNativeSqlType(parameter.ParameterType, parameter.GetCustomAttributes(true)) +
                                  defaultValue + ",\n";
                }
                parameters = parameters.TrimEnd(',', '\n');
                if (parameters.Length > 0)
                    parameters += '\n';
                string returnType = null;
                if (method.ReturnType != typeof(IEnumerable)
                    && method.ReturnType != typeof(IEnumerator))
                {
                    returnType = GetNativeSqlType(method.ReturnType,
                                                  method.ReturnTypeCustomAttributes.GetCustomAttributes(true));
                    if (!returnType.Contains("(") && !returnType.Contains(")"))
                        returnType = "[" + SafeSqlIdentifier(returnType) + "]";
                }
                else
                    returnType = "TABLE(" + functionAttribute.TableDefinition + ")";
                builder.AppendFormat(
                    @"IF (@@TRANCOUNT > 0)
BEGIN
	EXEC sp_executesql N'
		CREATE FUNCTION [{1}].[{2}]
		(
{3}		)
		RETURNS {4}
		AS
			EXTERNAL NAME [{5}].[{6}].[{7}]'
	{8}
END
{0}"
                    , scriptGo
                    , schemaName
                    , SafeSqlIdentifierAndQuotes(functionName)
                    , SafeSqlQuotes(parameters)
                    , SafeSqlQuotes(returnType)
                    , SafeSqlIdentifierAndQuotes(assemblyName)
                    , SafeSqlIdentifierAndQuotes(method.ReflectedType.FullName)
                    , SafeSqlIdentifierAndQuotes(method.Name)
                    , scriptIfErrorRollback
                    );
            }
        }

        private void BuildSqlProcedures(string assemblyName, MethodInfo[] methods, StringBuilder builder)
        {
            foreach (MethodInfo method in methods)
            {
                var attribute =
                    method.GetCustomAttributes(typeof(SqlProcedureAttribute), false)[0] as SqlProcedureAttribute;
                string procedureName = (attribute.Name != null) ? attribute.Name : method.Name;
                string parameters = string.Empty;
                foreach (ParameterInfo parameter in method.GetParameters())
                {
                    parameters += "\t\t\t@" + parameter.Name + " ";
                    string defaultValue = GetDefaultValue(parameter.GetCustomAttributes(true));
                    if (parameter.ParameterType.IsByRef)
                    {
                        parameters +=
                            GetNativeSqlType(parameter.ParameterType.GetElementType(),
                                             parameter.GetCustomAttributes(true)) + defaultValue;
                        parameters += " OUTPUT";
                    }
                    else
                        parameters += GetNativeSqlType(parameter.ParameterType, parameter.GetCustomAttributes(true)) +
                                      defaultValue;
                    parameters += ",\n";
                }
                parameters = parameters.TrimEnd(',', '\n');
                if (parameters.Length > 0)
                    parameters += '\n';
                builder.AppendFormat(
                    @"IF (@@TRANCOUNT > 0)
BEGIN
	EXEC sp_executesql N'
		CREATE PROCEDURE [{1}].[{2}]
{3}		AS
			EXTERNAL NAME [{4}].[{5}].[{6}]'
	{7}
END
{0}"
                    , scriptGo
                    , schemaName
                    , SafeSqlIdentifierAndQuotes(procedureName)
                    , SafeSqlQuotes(parameters)
                    , SafeSqlIdentifierAndQuotes(assemblyName)
                    , SafeSqlIdentifierAndQuotes(method.ReflectedType.FullName)
                    , SafeSqlIdentifierAndQuotes(method.Name)
                    , scriptIfErrorRollback
                    );
            }
        }

        private void BuildSqlTriggers(string assemblyName, MethodInfo[] methods, StringBuilder builder)
        {
            foreach (MethodInfo method in methods)
            {
                var attribute =
                    method.GetCustomAttributes(typeof(SqlTriggerAttribute), false)[0] as SqlTriggerAttribute;
                string triggerName = (attribute.Name != null) ? attribute.Name : method.Name;
                bool isDatabaseTrigger = attribute.Target.Trim().Equals("DATABASE",
                                                                        StringComparison.InvariantCultureIgnoreCase);
                var template = new StringBuilder();


                builder.AppendFormat(isDatabaseTrigger
                                         ?
                    // Cannot specify a schema name as a prefix to the trigger name for database and server level triggers.
                                     @"IF (@@TRANCOUNT > 0)
BEGIN
	EXEC sp_executesql N'
		CREATE TRIGGER [{2}] ON {3} {4}
		AS
			EXTERNAL NAME [{5}].[{6}].[{7}]'
	{8}
"
                                         :
                                             @"IF (@@TRANCOUNT > 0)
BEGIN
	EXEC sp_executesql N'
		CREATE TRIGGER [{1}].[{2}] ON [{3}] {4}
		AS
			EXTERNAL NAME [{5}].[{6}].[{7}]'
	{8}
	ELSE
	BEGIN
		DECLARE @targetType varchar(128)
		SELECT @targetType = CASE p.type WHEN 'U' THEN 'TABLE' WHEN 'V' THEN 'VIEW' ELSE '' END
			FROM sys.objects o
			INNER JOIN sys.objects p ON o.parent_object_id = p.object_id
			WHERE o.object_id = OBJECT_ID(N'[{1}].[{2}]')
	END
"
                                     , string.Empty
                                     , schemaName
                                     , SafeSqlIdentifierAndQuotes(triggerName)
                                     ,
                                     isDatabaseTrigger ? attribute.Target : SafeSqlIdentifierAndQuotes(attribute.Target)
                                     , SafeSqlQuotes(attribute.Event)
                                     , SafeSqlIdentifierAndQuotes(assemblyName)
                                     , SafeSqlIdentifierAndQuotes(method.ReflectedType.FullName)
                                     , SafeSqlIdentifierAndQuotes(method.Name)
                                     , scriptIfErrorRollback
                                     , SafeSqlQuotes(triggerName)
                    );

                builder.Append(
                    @"
END"
                    );
                builder.Append(scriptGo);
            }
        }

        private void BuildSqlTypes(string assemblyName, Type[] types, StringBuilder builder)
        {
            foreach (Type type in types)
            {
                var attribute =
                    type.GetCustomAttributes(typeof(SqlUserDefinedTypeAttribute), false)[0] as
                    SqlUserDefinedTypeAttribute;
                string typeName = (attribute.Name != null) ? attribute.Name : type.Name;
                builder.AppendFormat(
                    @"IF (@@TRANCOUNT > 0)
BEGIN
	EXEC sp_executesql N'
		CREATE TYPE [{1}].[{2}]
			EXTERNAL NAME [{3}].[{4}]'
	{5}
END
{0}"
                    , scriptGo
                    , schemaName
                    , SafeSqlIdentifierAndQuotes(typeName)
                    , SafeSqlIdentifierAndQuotes(assemblyName)
                    , SafeSqlIdentifierAndQuotes(type.FullName)
                    , scriptIfErrorRollback
                    );
            }
        }

        private static string GetNativeSqlType(Type type, object[] attributes)
        {
            SqlFacetAttribute facetAttribute = null;
            if (attributes != null)
            {
                foreach (Attribute attribute in attributes)
                {
                    if (attribute is SqlFacetAttribute)
                        facetAttribute = attribute as SqlFacetAttribute;
                }
            }
            switch (type.FullName)
            {
                case "System.Boolean":
                    return "bit";
                case "System.Byte":
                    return "tinyint";
                case "System.Char":
                    return "nchar(1)";
                case "System.Double":
                    return "float";
                case "System.Int16":
                    return "smallint";
                case "System.Int32":
                    return "int";
                case "System.Int64":
                    return "bigint";
                case "System.Single":
                    return "real";
                case "System.Guid":
                    return "uniqueidentifier";
                case "System.DateTime":
                    return "datetime";
                case "System.Object":
                    return "sql_variant";
                case "System.Data.SqlTypes.SqlBoolean":
                    return "bit";
                case "System.Data.SqlTypes.SqlGuid":
                    return "uniqueidentifier";
                case "System.Data.SqlTypes.SqlByte":
                    return "tinyint";
                case "System.Data.SqlTypes.SqlInt16":
                    return "smallint";
                case "System.Data.SqlTypes.SqlInt32":
                    return "int";
                case "System.Data.SqlTypes.SqlInt64":
                    return "bigint";
                case "System.Data.SqlTypes.SqlMoney":
                    return "money";
                case "System.Data.SqlTypes.SqlSingle":
                    return "real";
                case "System.Data.SqlTypes.SqlDouble":
                    return "float";
                case "System.Data.SqlTypes.SqlDateTime":
                    return "datetime";
                case "System.Data.SqlTypes.SqlXml":
                    return "xml";
                case "System.UInt16":
                case "System.UInt32":
                case "System.UInt64":
                case "System.SByte":
                    return null;
                case "System.Char[]":
                case "System.String":
                case "System.Data.SqlTypes.SqlString":
                    return GetNativeSqlType(facetAttribute, "char", 4000, true);
                case "System.Byte[]":
                case "System.Data.SqlTypes.SqlBinary":
                    return GetNativeSqlType(facetAttribute, "binary", 8000, false);
                case "System.Data.SqlTypes.SqlBytes":
                    return GetNativeSqlType(facetAttribute, "binary", -1, false);
                case "System.Data.SqlTypes.SqlChars":
                    return GetNativeSqlType(facetAttribute, "char", -1, true);
                case "System.Decimal":
                case "System.Data.SqlTypes.SqlDecimal":
                    if (facetAttribute != null)
                    {
                        return string.Format("numeric({0},{1})",
                                             (facetAttribute.Precision > 0)
                                                 ?
                                                     facetAttribute.Precision
                                                 :
                                                     0x12,
                                             (facetAttribute.Scale > 0)
                                                 ?
                                                     facetAttribute.Scale
                                                 :
                                                     0
                            );
                    }
                    return "numeric";
                default:
                    SqlUserDefinedTypeAttribute udfAttribute = null;
                    object[] udfAttributes = type.GetCustomAttributes(typeof(SqlUserDefinedTypeAttribute), false);
                    if (udfAttributes.Length > 0)
                        udfAttribute = udfAttributes[0] as SqlUserDefinedTypeAttribute;
                    if ((udfAttribute != null) && (udfAttribute.Name != null))
                        return udfAttribute.Name;
                    return type.Name;
            }
        }

        private static string GetNativeSqlType(SqlFacetAttribute facetAttribute, string baseType, int maxSize,
                                               bool unicode)
        {
            if (facetAttribute != null)
            {
                return string.Format("{0}{1}{2}({3})",
                                     (unicode ? "n" : string.Empty),
                                     (facetAttribute.IsFixedLength && (facetAttribute.MaxSize >= 0))
                                         ?
                                             string.Empty
                                         :
                                             "var",
                                     baseType,
                                     (facetAttribute.MaxSize > 0)
                                         ?
                                             facetAttribute.MaxSize.ToString()
                                         :
                                             ((facetAttribute.MaxSize < 0 || maxSize < 0)
                                                  ?
                                                      "MAX"
                                                  :
                                                      maxSize.ToString())
                    );
            }
            return string.Format("{0}var{1}({2})",
                                 unicode ? "n" : string.Empty,
                                 baseType,
                                 (maxSize < 0)
                                     ?
                                         "MAX"
                                     :
                                         maxSize.ToString()
                );
        }

        /// <summary>
        /// Custom attribute for deployment of SQL function and procedure parameters.
        /// </summary>
        /// <param name="attributes"></param>
        /// <returns></returns>
        private string GetDefaultValue(object[] attributes)
        {
            if (attributes != null)
            {
                foreach (Attribute attribute in attributes)
                {
                    if (attribute is DefaultValueAttribute)
                    {
                        var defaultValueAttribute = attribute as DefaultValueAttribute;
                        if (defaultValueAttribute.Value == null)
                            return " = NULL";
                        else if (defaultValueAttribute.Value is string)
                            return " = '" + defaultValueAttribute.Value + "'";
                        else
                            return " = " +
                                   Convert.ToString(defaultValueAttribute.Value,
                                                    CultureInfo.InvariantCulture.NumberFormat);
                    }
                    if (attribute is OptionalAttribute)
                        return " = NULL";
                }
            }
            return null;
        }

        private MethodInfo[] GetSqlMethods(Assembly assembly, Type attributeType)
        {
            return GetSqlMethods(assembly, attributeType, null);
        }

        private MethodInfo[] GetSqlMethods(Assembly assembly, Type attributeType, Type notAttributeType)
        {
            Type[] types = assembly.GetTypes();
            var list = new List<MethodInfo>();
            foreach (Type type in types)
            {
                if ((type.IsClass || type.IsValueType) || type.IsInterface)
                {
                    MethodInfo[] methods =
                        type.GetMethods(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static |
                                        BindingFlags.Instance);
                    foreach (MethodInfo method in methods)
                    {
                        object[] attributes = method.GetCustomAttributes(attributeType, false);
                        if ((attributes.Length > 0) &&
                            ((notAttributeType == null) || (attributes[0].GetType() != notAttributeType)))
                            list.Add(method);
                    }
                }
            }
            return list.ToArray();
        }

        private Type[] GetSqlTypes(Assembly assembly, Type attributeType)
        {
            Type[] types = assembly.GetTypes();
            var list = new List<Type>();
            foreach (Type type in types)
            {
                if (type.IsClass || type.IsValueType)
                {
                    object[] attributes = type.GetCustomAttributes(attributeType, false);
                    if (attributes.Length > 0)
                        list.Add(type);
                }
            }
            return list.ToArray();
        }

        private static string SafeSqlQuotes(string text)
        {
            return text.Replace("'", "''");
        }

        private static string SafeSqlIdentifier(string text)
        {
            return text.Replace("]", "]]");
        }

        private static string SafeSqlIdentifierAndQuotes(string text)
        {
            return text.Replace("]", "]]").Replace("'", "''");
        }

        #region Nested type: ConvertByteArrayToVarbinaryString

        internal delegate string ConvertByteArrayToVarbinaryString(byte[] byteArray);

        #endregion
    }
}